from openpyxl import load_workbook
import os
import sqlite3
import pinyin

wb = load_workbook(os.path.dirname(__file__)+"/template.xlsx")
#db=sqlite3.connect(os.path.dirname(__file__)+"/template.db")
db=sqlite3.connect(":memory:")
cur=db.cursor()
try:
    db.execute('''
    create table tab(
        rid integer primary key autoincrement,
        username char(10) null,
        sid char(16),
        bj char(8),
        py char(255),
        cj char (6)
    )
    ''')
except:
    db.rollback()
db.commit()
list=wb.sheetnames
print(list)
dbl=[]
sheet = wb.get_sheet_by_name(list[0])
print(sheet.max_row)
for v in range(2,sheet.max_row+1):
    print(sheet.cell(v,3).value)
    inpy=",".join(pinyin.getpys(sheet.cell(v,3).value,3))
    print(inpy)
    dbl.append((sheet.cell(v,2).value,sheet.cell(v,3).value,sheet.cell(v,4).value))
    db.execute("insert into tab(sid,username,bj,py) values('%s','%s','%s','%s')"%(sheet.cell(v,2).value,sheet.cell(v,3).value,sheet.cell(v,4).value,inpy))
#print(dbl)
#db.executemany("insert into tab(sid,username,bj) values('%s','%s','%s')",dbl)
#db.execute("insert into tab(sid,username,bj) values('%s','%s','%s')"%(sheet.cell(v,2).value,sheet.cell(v,3).value,sheet.cell(v,4).value))
db.commit()
    

wb.save(os.path.dirname(__file__)+"/new.xlsx")
while True:
    a=input("name:")
    print(a)
    sql="select * from tab where py like '%{0}%'".format(a)
    cur.execute(sql)
    re=cur.fetchall()
    for i in re:
        print(i)
cur.close()
db.close()